-- @author lixiyong01
-- @date 2023.03.18
-- 小文件数占比大于35%并且小文件数大于1825需要进行处理

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_table_small_file partition(dt = '${dateSuffix}')
select table_id,
    user_name as owner_id,
    db_name as database_name,
    tbl_name as table_name,
    is_partition,
    lifecycle,
    file_nums,
    small_file_nums,
    round(small_file_nums/file_nums*100,2) as small_ratio,
    round(filesize/1024/1024/1024,2) as filesize_gb,
    location,
    is_claim,
    access_last_time,
    create_time
from hdp_lbg_supin_zplisting.dwd_zp_rock_table_meta_info
where dt='${#date(0,0,-1):yyyyMMdd#}'
and status=1
and small_file_nums/file_nums > 0.35 and small_file_nums>1825
and tbl_name not like 'raw_%'